﻿declare @lastDate nvarchar(8) = convert(nvarchar(8), (DATEADD(yy, -1, (DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)))),112)
declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])

--select * FROM FT_HIST
INSERT FT_HIST
SELECT [BUSINESS_DATE],
		[ID],
		[TRANSACTION_TYPE],
		[FT_SECTOR],
		[CO_CODE],
		[CREDIT_CIF],
		[CREDIT_VALUE_DATE],
		[CREDIT_ACCOUNT],[CREDIT_CCY],
		[CREDIT_AMOUNT],
		[DEBIT_ACCOUNT],[DEBIT_CCY],
		[DEBIT_VALUE_DATE],
		[DEBIT_CIF], 
		[DEBIT_AMOUNT] 
FROM SERVER16.[VPB_WHR2].[DBO].[FT_HIST] 
WHERE BUSINESS_DATE > (SELECT MAX(BUSINESS_DATE) FROM FT_HIST) AND BUSINESS_DATE <=  @DATE_UPDATE

DROP TABLE FT_HIST_ACCOUNT_PLANNING

/* chuyển tiền quốc tế*/

SELECT * INTO FT_HIST_ACCOUNT_PLANNING 
FROM FT_HIST 
	WHERE FT_SECTOR ='DOANH NGHIEP' 
		AND CO_CODE NOT IN (SELECT BRANCH_CODE FROM [ANHCP].[CEO_PERFORMANCE].[DBO].[BRANCHOFF])
		AND ((TRANSACTION_TYPE = 'IT' AND CREDIT_ACCOUNT NOT LIKE '%1300700020001%') OR TRANSACTION_TYPE = 'OT')

DELETE FROM FT_HIST_CHUYEN_TIEN_QT

INSERT INTO FT_HIST_CHUYEN_TIEN_QT
SELECT  LEFT(CONVERT(VARCHAR(8), BUSINESS_DATE,112),6) AS YEARMONTH,
		BUSINESS_DATE,
		TRANSACTION_TYPE, 
		CREDIT_CIF AS CIF, 
		CREDIT_CCY AS CCY, 
		CREDIT_AMOUNT AS AMOUNT, 
		(A.CREDIT_AMOUNT*B.RATE) AS AMOUNT_QD
		--INTO FT_HIST_CHUYEN_TIEN_QT
		FROM FT_HIST_ACCOUNT_PLANNING AS A, [FOCURR_SAVE] AS B
		 WHERE TRANSACTION_TYPE = 'IT'AND A.BUSINESS_DATE = B.SAVE_DATE AND A.CREDIT_CCY = B.CODE 
		 AND A.CREDIT_CIF IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)

INSERT INTO FT_HIST_CHUYEN_TIEN_QT
SELECT  LEFT(CONVERT(VARCHAR(8), BUSINESS_DATE,112),6) AS YEARMONTH,
		BUSINESS_DATE,
		TRANSACTION_TYPE, 
		DEBIT_CIF AS CIF, 
		DEBIT_CCY AS CCY, 
		DEBIT_AMOUNT AS AMOUNT,
		(A.DEBIT_AMOUNT*B.RATE) AS AMOUNT_QD
		FROM FT_HIST_ACCOUNT_PLANNING AS A, [FOCURR_SAVE] AS B
		WHERE TRANSACTION_TYPE = 'OT' AND A.BUSINESS_DATE = B.SAVE_DATE AND A.DEBIT_CCY = B.CODE
		AND A.DEBIT_CIF IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)
/* chuyển tiền trong nước*/

--DROP TABLE FT_HIST_ACCOUNT_PLANNING_DOMESTIC_TRANSFER
--SELECT LEFT(CONVERT(VARCHAR(8), BUSINESS_DATE,112),6) AS YEARMONTH,
--		BUSINESS_DATE,
--		TRANSACTION_TYPE, 
--		CREDIT_CIF AS CIF, 
--		CREDIT_CCY AS CCY, 
--		CREDIT_AMOUNT AS AMOUNT, 
--		(A.CREDIT_AMOUNT*B.RATE) AS AMOUNT_QD
--		 INTO FT_HIST_ACCOUNT_PLANNING_DOMESTIC_TRANSFER
--FROM FT_HIST AS A, [FOCURR_SAVE] AS B
-- WHERE 
--	TRANSACTION_TYPE IN ('BIIB','BIID','BIPC','BIVC','BIVI','BCIB','BCPC','OTVC','OTVI','OTID','OTAR')
--	AND A.BUSINESS_DATE = B.SAVE_DATE
--	AND DEBIT_CIF IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)

DELETE FROM FT_HIST_ACCOUNT_PLANNING_DOMESTIC_TRANSFER

INSERT INTO FT_HIST_ACCOUNT_PLANNING_DOMESTIC_TRANSFER
SELECT  LEFT(CONVERT(VARCHAR(8), BUSINESS_DATE,112),6) AS YEARMONTH,
		BUSINESS_DATE,
		TRANSACTION_TYPE, 
		CREDIT_CIF AS CIF, 
		CREDIT_CCY AS CCY, 
		CREDIT_AMOUNT AS AMOUNT, 
		(A.CREDIT_AMOUNT*B.RATE) AS AMOUNT_QD
		--INTO FT_HIST_ACCOUNT_PLANNING_DOMESTIC_TRANSFER
		FROM FT_HIST AS A, [FOCURR_SAVE] AS B
		 WHERE TRANSACTION_TYPE LIKE '%BI%'
		 AND A.BUSINESS_DATE = B.SAVE_DATE AND A.CREDIT_CCY = B.CODE 
		 AND A.CREDIT_CIF IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)

INSERT INTO FT_HIST_ACCOUNT_PLANNING_DOMESTIC_TRANSFER
SELECT  LEFT(CONVERT(VARCHAR(8), BUSINESS_DATE,112),6) AS YEARMONTH,
		BUSINESS_DATE,
		TRANSACTION_TYPE, 
		DEBIT_CIF AS CIF, 
		DEBIT_CCY AS CCY, 
		DEBIT_AMOUNT AS AMOUNT,
		(A.DEBIT_AMOUNT*B.RATE) AS AMOUNT_QD
		FROM FT_HIST AS A, [FOCURR_SAVE] AS B
		WHERE TRANSACTION_TYPE IN ('BCIB','BCPC','OTVC','OTVI','OTID','OTAR')
		 AND A.BUSINESS_DATE = B.SAVE_DATE AND A.DEBIT_CCY = B.CODE
		AND A.DEBIT_CIF IN (SELECT CIF FROM TBL_ACCOUNT_PLANNING)


--SELECT * FROM FT_HIST_ACCOUNT_PLANNING_DOMESTIC_TRANSFER
--select * from FT_HIST where TRANSACTION_TYPE IN ('BIIB','BIID','BIPC','BIVC','BIVI','BCIB','BCPC','OTVC','OTVI','OTID','OTAR')